﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using KPIS.GERP.GAMS.MODEL;
using KPIS.DBM;
using System.Configuration;
using System.Data;

namespace KPIS.GERP.GAMS.DAL
{
    public class D_ACM_ACC_CHANGE_Value
    {
        DBManager dbManager = new DBManager(SystemInfo._DataProvider, ConfigurationSettings.AppSettings["ConnectionString"].ToString());
        ConvertNullable cn = new ConvertNullable();

        //public IList<M_ACM_ACC_CHANGE_Value> GetList()
        //{
        //    DataRowCollection drc = null;
        //    IList<M_ACM_ACC_CHANGE_Value> infoList = new List<M_ACM_ACC_CHANGE_Value>();
        //    try
        //    {
        //        dbManager.Open();
        //        string strSQL = "SELECT account_change_seq,account_cash,account_creditor_from_check_receive,account_wht_tax "
        //            + " account_voucher_remain_pay,account_pay_between_org,account_income_between_org "
        //            + " ,account_fine_from_check,account_gurantee_result,account_wait_receive_cash "
        //            + " ,account_request_money_more_wait_return,account_guarantee_envelope "
        //            + " ,account_guarantee_promise,account_guarantee_pay_advance,account_send_income "
        //            + " , account_income_wait_send_stock,account_cash_deposit_stock,tax_id "
        //            + " FROM acm_change_value "
        //            + " WHERE record_status not like 'D'"
        //            + " ORDER BY account_change_seq ";

        //        drc = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0].Rows;
        //        if (drc.Count > 0)
        //        {
        //            for (int i = 0; i < drc.Count; i++)
        //            {
        //                M_ACM_ACC_CHANGE_Value info = new M_ACM_ACC_CHANGE_Value();
        //                info.account_change_seq = Convert.ToInt32(drc[i]["account_change_seq"]);
        //                info.account_cash = cn.ConvertNullInt(drc[i]["account_cash"]);
        //                info.account_creditor_from_check_receive = cn.ConvertNullInt(drc[i]["account_creditor_from_check_receive"]);
        //                info.account_wht_tax = cn.ConvertNullInt(drc[i]["account_wht_tax"]);
        //                info.account_voucher_remain_pay = cn.ConvertNullInt(drc[i]["account_voucher_remain_pay"]);
        //                info.account_pay_between_org = cn.ConvertNullInt(drc[i]["account_pay_between_org"]);
        //                info.account_income_between_org = cn.ConvertNullInt(drc[i]["account_income_between_org"]);
        //                info.account_fine_from_check = cn.ConvertNullInt(drc[i]["account_fine_from_check"]);
        //                info.account_gurantee_result = cn.ConvertNullInt(drc[i]["account_gurantee_result"]);
        //                info.account_wait_receive_cash = cn.ConvertNullInt(drc[i]["account_wait_receive_cash"]);
        //                info.account_request_money_more_wait_return = cn.ConvertNullInt(drc[i]["account_request_money_more_wait_return"]);
        //                info.account_guarantee_envelope = cn.ConvertNullInt(drc[i]["account_guarantee_envelope"]);
        //                info.account_guarantee_promise = cn.ConvertNullInt(drc[i]["account_guarantee_promise"]);
        //                info.account_guarantee_pay_advance = cn.ConvertNullInt(drc[i]["account_guarantee_pay_advance"]);
        //                info.account_send_income = cn.ConvertNullInt(drc[i]["account_send_income"]);
        //                info.account_income_wait_send_stock = cn.ConvertNullInt(drc[i]["account_income_wait_send_stock"]);
        //                info.account_cash_deposit_stock = cn.ConvertNullInt(drc[i]["account_cash_deposit_stock"]);
        //                info.tax_id = drc[i]["tax_id"].ToString();
        //                infoList.Add(info);
        //            }
        //        }
        //    }
        //    catch (Exception ex)
        //    {
        //        throw (ex);
        //    }
        //    finally
        //    {
        //        dbManager.Dispose();
        //    }
        //    return infoList;
        //}

        public M_ACM_ACC_CHANGE_Value GetList()
        {
            DataRowCollection drc = null;
            M_ACM_ACC_CHANGE_Value info = new M_ACM_ACC_CHANGE_Value();
            try
            {
                dbManager.Open();

                string strSQL = "SELECT account_change_seq, account_cash, account_creditor_from_check_receive"
                    + ", account_debtor_borrow_cash, account_wht_tax1,account_wht_tax2 "
                    + ", account_voucher_remain_pay, account_pay_between_org,account_income_between_org "
                    + ", account_fine_from_check, account_gurantee_result,account_wait_receive_cash "
                    + ", account_request_money_more_wait_return, account_guarantee_envelope "
                    + ", account_guarantee_promise, account_guarantee_pay_advance,account_send_income "
                    + ", account_income_wait_send_stock, account_cash_deposit_stock,account_remain_receive_org_acc_center,length_account_code"
                    + " FROM acm_change_value ";

                drc = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0].Rows;
                if (drc.Count > 0)
                {
                        info.account_change_seq = Convert.ToInt32(drc[0]["account_change_seq"]);
                        info.account_cash = cn.ConvertNullInt(drc[0]["account_cash"]);
                        info.account_creditor_from_check_receive = cn.ConvertNullInt(drc[0]["account_creditor_from_check_receive"]);
                        info.account_debtor_borrow_cash = cn.ConvertNullInt(drc[0]["account_debtor_borrow_cash"]);
                        info.account_wht_tax1 = cn.ConvertNullInt(drc[0]["account_wht_tax1"]);
                        info.account_wht_tax2 = cn.ConvertNullInt(drc[0]["account_wht_tax2"]);
                        info.account_voucher_remain_pay = cn.ConvertNullInt(drc[0]["account_voucher_remain_pay"]);
                        info.account_pay_between_org = cn.ConvertNullInt(drc[0]["account_pay_between_org"]);
                        info.account_income_between_org = cn.ConvertNullInt(drc[0]["account_income_between_org"]);
                        info.account_fine_from_check = cn.ConvertNullInt(drc[0]["account_fine_from_check"]);
                        info.account_gurantee_result = cn.ConvertNullInt(drc[0]["account_gurantee_result"]);
                        info.account_wait_receive_cash = cn.ConvertNullInt(drc[0]["account_wait_receive_cash"]);
                        info.account_request_money_more_wait_return = cn.ConvertNullInt(drc[0]["account_request_money_more_wait_return"]);
                        info.account_guarantee_envelope = cn.ConvertNullInt(drc[0]["account_guarantee_envelope"]);
                        info.account_guarantee_promise = cn.ConvertNullInt(drc[0]["account_guarantee_promise"]);
                        info.account_guarantee_pay_advance = cn.ConvertNullInt(drc[0]["account_guarantee_pay_advance"]);
                        info.account_send_income = cn.ConvertNullInt(drc[0]["account_send_income"]);
                        info.account_income_wait_send_stock = cn.ConvertNullInt(drc[0]["account_income_wait_send_stock"]);
                        info.account_cash_deposit_stock = cn.ConvertNullInt(drc[0]["account_cash_deposit_stock"]);
                        info.account_remain_receive_org_acc_center = cn.ConvertNullInt(drc[0]["account_remain_receive_org_acc_center"]);
                        info.length_account_code = cn.ConvertNullInt(drc[0]["length_account_code"]);
                }
            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                dbManager.Dispose();
            }
            return info;
        }


        public M_ACM_ACC_CHANGE_Value GetMaxSeq()
        {
            string strSQL;
            DataRowCollection drc = null;
            M_ACM_ACC_CHANGE_Value info = new M_ACM_ACC_CHANGE_Value();
            try
            {
                dbManager.Open();
                strSQL = " SELECT COUNT(account_change_seq) as count_account "
                        + " FROM acm_change_value "
                        + " WHERE record_status not like 'D' ";

                drc = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0].Rows;
                if (drc.Count > 0)
                {
                    info.maxaccount_seq = Convert.ToInt32(drc[0]["count_account"]);
                }
            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                dbManager.Dispose();
            }
            return info;
        }




        public void INSERT(M_ACM_ACC_CHANGE_Value info)
        {
            string strSQL;
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(23);
                dbManager.AddParameters(0, "@account_cash", info.account_cash);
                dbManager.AddParameters(1, "@account_creditor_from_check_receive", info.account_creditor_from_check_receive);
                dbManager.AddParameters(2, "@account_debtor_borrow_cash", info.account_debtor_borrow_cash);
                dbManager.AddParameters(3, "@account_wht_tax1", info.account_wht_tax1);
                dbManager.AddParameters(4, "@account_voucher_remain_pay", info.account_voucher_remain_pay);
                dbManager.AddParameters(5, "@account_pay_between_org", info.account_pay_between_org);
                dbManager.AddParameters(6, "@account_income_between_org", info.account_income_between_org);
                dbManager.AddParameters(7, "@account_fine_from_check", info.account_fine_from_check);
                dbManager.AddParameters(8, "@account_gurantee_result", info.account_gurantee_result);
                dbManager.AddParameters(9, "@account_wait_receive_cash", info.account_wait_receive_cash);
                dbManager.AddParameters(10, "@account_request_money_more_wait_return", info.account_request_money_more_wait_return);
                dbManager.AddParameters(11, "@account_guarantee_envelope", info.account_guarantee_envelope);
                dbManager.AddParameters(12, "@account_guarantee_promise", info.account_guarantee_promise);
                dbManager.AddParameters(13, "@account_guarantee_pay_advance", info.account_guarantee_pay_advance);
                dbManager.AddParameters(14, "@account_send_income", info.account_send_income);
                dbManager.AddParameters(15, "@account_income_wait_send_stock", info.account_income_wait_send_stock);
                dbManager.AddParameters(16, "@account_cash_deposit_stock", info.account_cash_deposit_stock);
                dbManager.AddParameters(17, "@account_remain_receive_org_acc_center", info.account_remain_receive_org_acc_center);
                dbManager.AddParameters(18, "@length_account_code", info.length_account_code);
                dbManager.AddParameters(19, "@record_status", info.record_status);
                dbManager.AddParameters(20, "@created_by", info.created_by);
                dbManager.AddParameters(21, "@created_when", info.created_when);
                dbManager.AddParameters(22, "@account_wht_tax2", info.account_wht_tax2);

                strSQL = " INSERT INTO acm_change_value "
                        + "( account_cash,  account_creditor_from_check_receive,  account_debtor_borrow_cash, account_wht_tax1, account_wht_tax2"
                        + " ,account_voucher_remain_pay, account_pay_between_org, account_income_between_org, account_fine_from_check "
                        + ",account_gurantee_result, account_wait_receive_cash, account_request_money_more_wait_return "
                        + " ,account_guarantee_envelope, account_guarantee_promise, account_guarantee_pay_advance "
                        + " ,account_send_income, account_income_wait_send_stock, account_cash_deposit_stock,account_remain_receive_org_acc_center,length_account_code"
                        + " ,record_status,created_by,created_when) "
                        + " VALUES ( @account_cash,  @account_creditor_from_check_receive,  @account_debtor_borrow_cash, @account_wht_tax1, @account_wht_tax2"
                        + " ,@account_voucher_remain_pay, @account_pay_between_org, @account_income_between_org, @account_fine_from_check "
                        + " ,@account_gurantee_result, @account_wait_receive_cash, @account_request_money_more_wait_return "
                        + " ,@account_guarantee_envelope, @account_guarantee_promise, @account_guarantee_pay_advance "
                        + " ,@account_send_income, @account_income_wait_send_stock, @account_cash_deposit_stock ,@account_remain_receive_org_acc_center,@length_account_code"
                        + " ,@record_status,@created_by,@created_when) ";

                dbManager.ExecuteNonQuery(CommandType.Text, strSQL);
            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                dbManager.Dispose();
            }
       }
        public void UPDATE(M_ACM_ACC_CHANGE_Value info)
        {
            string strSQL;
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(24);
                dbManager.AddParameters(0, "@account_change_seq", info.account_change_seq);
                dbManager.AddParameters(1, "@account_cash", info.account_cash);
                dbManager.AddParameters(2, "@account_creditor_from_check_receive", info.account_creditor_from_check_receive);
                dbManager.AddParameters(3, "@account_debtor_borrow_cash", info.account_debtor_borrow_cash);
                dbManager.AddParameters(4, "@account_wht_tax1", info.account_wht_tax1);
                dbManager.AddParameters(5, "@account_voucher_remain_pay", info.account_voucher_remain_pay);
                dbManager.AddParameters(6, "@account_pay_between_org", info.account_pay_between_org);
                dbManager.AddParameters(7, "@account_income_between_org", info.account_income_between_org);
                dbManager.AddParameters(8, "@account_fine_from_check", info.account_fine_from_check);
                dbManager.AddParameters(9, "@account_gurantee_result", info.account_gurantee_result);
                dbManager.AddParameters(10, "@account_wait_receive_cash", info.account_wait_receive_cash);
                dbManager.AddParameters(11, "@account_request_money_more_wait_return", info.account_request_money_more_wait_return);
                dbManager.AddParameters(12, "@account_guarantee_envelope", info.account_guarantee_envelope);
                dbManager.AddParameters(13, "@account_guarantee_promise", info.account_guarantee_promise);
                dbManager.AddParameters(14, "@account_guarantee_pay_advance", info.account_guarantee_pay_advance);
                dbManager.AddParameters(15, "@account_send_income", info.account_send_income);
                dbManager.AddParameters(16, "@account_income_wait_send_stock", info.account_income_wait_send_stock);
                dbManager.AddParameters(17, "@account_cash_deposit_stock", info.account_cash_deposit_stock);
                dbManager.AddParameters(18, "@account_remain_receive_org_acc_center", info.account_remain_receive_org_acc_center);
                dbManager.AddParameters(19, "@length_account_code", info.length_account_code);
                dbManager.AddParameters(20, "@record_status", info.record_status);
                dbManager.AddParameters(21, "@updated_by", info.updated_by);
                dbManager.AddParameters(22, "@updated_when", info.updated_when);
                dbManager.AddParameters(23, "@account_wht_tax2", info.account_wht_tax2);

                strSQL = "UPDATE acm_change_value "
                         + " SET account_cash = @account_cash "
                         + " ,account_creditor_from_check_receive = @account_creditor_from_check_receive "
                         + " ,account_debtor_borrow_cash = @account_debtor_borrow_cash "
                         + " ,account_wht_tax1 = @account_wht_tax1"
                         + " ,account_wht_tax2 = @account_wht_tax2"
                         + " ,account_voucher_remain_pay = @account_voucher_remain_pay "
                         + " ,account_pay_between_org = @account_pay_between_org "
                         + " ,account_income_between_org = @account_income_between_org "
                         + " ,account_fine_from_check = @account_fine_from_check "
                         + " ,account_gurantee_result = @account_gurantee_result "
                         + " ,account_wait_receive_cash = @account_wait_receive_cash "
                         + " ,account_request_money_more_wait_return = @account_request_money_more_wait_return "
                         + " ,account_guarantee_envelope = @account_guarantee_envelope "
                         + " ,account_guarantee_promise = @account_guarantee_promise "
                         + " ,account_guarantee_pay_advance = @account_guarantee_pay_advance "
                         + " ,account_send_income = @account_send_income "
                         + " ,account_income_wait_send_stock = @account_income_wait_send_stock "
                         + " ,account_cash_deposit_stock = @account_cash_deposit_stock "
                         + " ,account_remain_receive_org_acc_center = @account_remain_receive_org_acc_center "
                         + " ,length_account_code = @length_account_code "
                         + " ,record_status = @record_status "
                         + " ,updated_by = @updated_by "
                         + " ,updated_when = @updated_when ";

                dbManager.ExecuteNonQuery(CommandType.Text, strSQL);
            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        public DataTable Report_ACCOUNT_CHANGE_Value()
        {
            string strSQL;
            DataTable dtb = null;
            try
            {
                dbManager.Open();

                strSQL = "SELECT concat(coalesce(acc1.account_code, ''), ' : ', coalesce(acc1.account_name, '')) as account_cash "
                        + ", concat(coalesce(acc2.account_code, ''), ' : ', coalesce(acc2.account_name, '')) as account_creditor_from_check_receive "
                        + ", concat(coalesce(acc3.account_code, ''), ' : ', coalesce(acc3.account_name, '')) as account_debtor_borrow_cash "
                        + ", concat(coalesce(acc4.account_code, ''), ' : ', coalesce(acc4.account_name, '')) as account_wht_tax1 "
                        + ", concat(coalesce(acc19.account_code, ''), ' : ', coalesce(acc19.account_name, '')) as account_wht_tax2 "
                        + ", concat(coalesce(acc5.account_code, ''), ' : ', coalesce(acc5.account_name, '')) as account_voucher_remain_pay "
                        + ", concat(coalesce(acc6.account_code, ''), ' : ', coalesce(acc6.account_name, '')) as account_pay_between_org "
                        + ", concat(coalesce(acc7.account_code, ''), ' : ', coalesce(acc7.account_name, '')) as account_income_between_org "
                        + ", concat(coalesce(acc8.account_code, ''), ' : ', coalesce(acc8.account_name, '')) as account_fine_from_check "
                        + ", concat(coalesce(acc9.account_code, ''), ' : ', coalesce(acc9.account_name, '')) as account_gurantee_result "
                        + ", concat(coalesce(acc10.account_code, ''), ' : ', coalesce(acc10.account_name, '')) as account_wait_receive_cash "
                        + ", concat(coalesce(acc11.account_code, ''), ' : ', coalesce(acc11.account_name, '')) as account_request_money_more_wait_return "
                        + ", concat(coalesce(acc12.account_code, ''), ' : ', coalesce(acc12.account_name, '')) as account_guarantee_envelope "
                        + ", concat(coalesce(acc13.account_code, ''), ' : ', coalesce(acc13.account_name, '')) as account_guarantee_promise "
                        + ", concat(coalesce(acc14.account_code, ''), ' : ', coalesce(acc14.account_name, '')) as account_guarantee_pay_advance "
                        + ", concat(coalesce(acc15.account_code, ''), ' : ', coalesce(acc15.account_name, '')) as account_send_income "
                        + ", concat(coalesce(acc16.account_code, ''), ' : ', coalesce(acc16.account_name, '')) as account_income_wait_send_stock "
                        + ", concat(coalesce(acc17.account_code, ''), ' : ', coalesce(acc17.account_name, '')) as account_cash_deposit_stock "
                        + ", concat(coalesce(acc18.account_code, ''), ' : ', coalesce(acc18.account_name, '')) as account_remain_receive_org_acc_center "
                        + " FROM acm_change_value acc "
                        + " left join acm_mas_account acc1 on "
                        + " acc.account_cash = acc1.account_seq "
                        + " left join acm_mas_account acc2 on "
                        + " acc.account_creditor_from_check_receive = acc2.account_seq "
                        + " left join acm_mas_account acc3 on "
                        + " acc.account_debtor_borrow_cash = acc3.account_seq "
                        + " left join acm_mas_account acc4 on "
                        + " acc.account_wht_tax1 = acc4.account_seq "
                        + " left join acm_mas_account acc19 on "
                        + " acc.account_wht_tax2 = acc19.account_seq "
                        + " left join acm_mas_account acc5 on "
                        + " acc.account_voucher_remain_pay = acc5.account_seq "
                        + " left join acm_mas_account acc6 on "
                        + " acc.account_pay_between_org = acc6.account_seq "
                        + " left join acm_mas_account acc7 on "
                        + " acc.account_income_between_org = acc7.account_seq "
                        + " left join acm_mas_account acc8 on "
                        + " acc.account_fine_from_check = acc8.account_seq "
                        + " left join acm_mas_account acc9 on "
                        + " acc.account_gurantee_result = acc9.account_seq "
                        + " left join acm_mas_account acc10 on "
                        + " acc.account_wait_receive_cash = acc10.account_seq "
                        + " left join acm_mas_account acc11 on "
                        + " acc.account_request_money_more_wait_return = acc11.account_seq "
                        + " left join acm_mas_account acc12 on "
                        + " acc.account_guarantee_envelope = acc12.account_seq "
                        + " left join acm_mas_account acc13 on "
                        + " acc.account_guarantee_promise = acc13.account_seq "
                        + " left join acm_mas_account acc14 on "
                        + " acc.account_guarantee_pay_advance = acc14.account_seq "
                        + " left join acm_mas_account acc15 on "
                        + " acc.account_send_income = acc15.account_seq "
                        + " left join acm_mas_account acc16 on "
                        + " acc.account_income_wait_send_stock = acc16.account_seq "
                        + " left join acm_mas_account acc17 on "
                        + " acc.account_cash_deposit_stock = acc17.account_seq "
                        + " left join acm_mas_account acc18 on "
                        + " acc.account_remain_receive_org_acc_center = acc18.account_seq ";

                dtb = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0];
            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                dbManager.Dispose();
            }

            return dtb;
        }

    }
}
